﻿<%@ WebHandler Language="C#" Class="RBContainerShipPlanReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;

/**
 * Description: 热表转工排产报表（生产部考核）
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.7.22
 **/

public class RBContainerShipPlanReport : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getCompute":
                        r = getCompute(jObject);//查询统计数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getChart":
                        r = getChart(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getChartDetail":
                        r = getChartDetail(jObject);//查询统计数据明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getSendNotInTimeChartData":
                        r = getSendNotInTimeChartData(jObject);//主制分厂未及时发出数据统计
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getSendNotInTimeDetails":
                        r = getSendNotInTimeDetails(jObject);//主制分厂未及时发出明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    case "exportSendNotInTimeDetails":
                        exportSendNotInTimeDetails(jObject);//主制分厂未及时发出明细--导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    // 计算排产率统计数据
    Result getCompute(JObject jObject)
    {
        Result r = new Result();
        DataTable dtRate = dal.GetDataTable(getSearchSqlString(jObject));
        if (dtRate.Rows.Count == 0)
        {
            r.data = new DataTable();
            r.total = 0;
            r.meta = new Result.Meta(200, "请求成功");
            return r;
        }

        foreach (DataRow dr in dtRate.Rows)
        {
            if (Convert.ToDecimal(dr["senddelaydays"]) < 0)
            {
                dr["senddelaydays"] = 0;
            }
            if (Convert.ToDecimal(dr["finishdelaydays"]) < 0)
            {
                dr["finishdelaydays"] = 0;
            }
        }

        DataTable dtCountInfo = new DataTable();
        dtCountInfo.Columns.Add("productCount");//排产零件数
        dtCountInfo.Columns.Add("specCount");//排产工序数
        dtCountInfo.Columns.Add("finishSpecCount");//完工工序数
        dtCountInfo.Columns.Add("delayFinishSpecCount");//超期完工数
        dtCountInfo.Columns.Add("delaySpecCount");//超期工序数
        dtCountInfo.Columns.Add("notFinishSpecCount");//未完工序数
        dtCountInfo.Columns.Add("delaydaySum");//超期天数
        dtCountInfo.Columns.Add("finishRate");//完成率
        dtCountInfo.Columns.Add("intimeFinishRate");//按时完成率
        dtCountInfo.Columns.Add("delayFinishRate");//超期完成率

        dtCountInfo.Columns.Add("intimeFinishSpecCount");//按时完成数
        dtCountInfo.Columns.Add("notFinishRate");//未完成率

        dtCountInfo.Columns.Add("specCountNew");//排产工序数（新）
        dtCountInfo.Columns.Add("finishSpecCountNew");//完工工序数（新）
        dtCountInfo.Columns.Add("delayFinishSpecCountNew");//超期完工数（新）
        dtCountInfo.Columns.Add("intimeFinishSpecCountNew");//按时完工数（新）
        dtCountInfo.Columns.Add("finishRateNew");//完成率（新）
        dtCountInfo.Columns.Add("intimeFinishRateNew");//按时完成率（新）

        DataRow datarow = dtCountInfo.NewRow();
        dtCountInfo.Rows.Add(datarow);

        //计算排产零件数
        int productCount = dtRate.DefaultView.ToTable(true, "productname").Rows.Count;
        datarow["productCount"] = productCount;

        //计算排产工序数
        int specCount = dtRate.Rows.Count;
        datarow["specCount"] = specCount;

        //计算完工工序数
        int finishSpecCount = dtRate.Select(" recivedate is not null ").Length;
        datarow["finishSpecCount"] = finishSpecCount;

        //计算超期完工数
        int delayFinishSpecCount = dtRate.Select(" recivedate is not null and finishdelaydays <> 0 ").Length;
        datarow["delayFinishSpecCount"] = delayFinishSpecCount;

        //计算超期工序数
        int delaySpecCount = dtRate.Select(" finishdelaydays <> 0 ").Length;
        datarow["delaySpecCount"] = delaySpecCount;

        //计算未完工序数
        int notFinishSpecCount = dtRate.Select("recivedate is null").Length;
        datarow["notFinishSpecCount"] = notFinishSpecCount;

        //计算超期天数
        decimal delaydaySum = Convert.ToDecimal(dtRate.Compute("Sum(finishdelaydays)", ""));
        datarow["delaydaySum"] = delaydaySum;

        // 计算完成率
        if (specCount > 0)
        {
            decimal finishRate = ((decimal)finishSpecCount / specCount) * 100;// 完成率 = 完工工序数 / 工序总数
            datarow["finishRate"] = finishRate.ToString("0.00");
        }
        else
        {
            datarow["finishRate"] = "--";
        }

        // 计算按时完成率
        if (specCount > 0)
        {
            decimal delayFinishRate = ((decimal)delayFinishSpecCount / specCount) * 100;// 超期完成率 = 超期完成率 / 工序总数
            datarow["delayFinishRate"] = delayFinishRate.ToString("0.00");

            decimal intimeFinishRate = ((decimal)(finishSpecCount - delayFinishSpecCount) / specCount) * 100;
            datarow["intimeFinishRate"] = intimeFinishRate.ToString("0.00");//按时完成率
        }
        else
        {
            datarow["delayFinishRate"] = "--";
            datarow["intimeFinishRate"] = "--";
        }

        //计算未完成率
        if (datarow["finishRate"].ToString() != "--")
        {
            datarow["notFinishRate"] = 100 - Convert.ToDecimal(datarow["finishRate"]);
        }

        //计算按时完成数
        datarow["intimeFinishSpecCount"] = Convert.ToInt32(datarow["finishSpecCount"]) - Convert.ToInt32(datarow["delayFinishSpecCount"]);

        //不过滤未及时发出的数据
        DataTable dtRateNew = dal.GetDataTable(getSearchSqlString(jObject, false));
        //计算排产工序数(新)
        int specCountNew = dtRateNew.Rows.Count;
        datarow["specCountNew"] = specCountNew;

        //计算完工工序数(新)
        int finishSpecCountNew = dtRateNew.Select(" recivedate is not null ").Length;
        datarow["finishSpecCountNew"] = finishSpecCountNew;

        //计算超期完工数(新)
        int delayFinishSpecCountNew = dtRateNew.Select(" recivedate is not null and finishdelaydays > 0 ").Length;
        datarow["delayFinishSpecCountNew"] = delayFinishSpecCountNew;

        //计算按时完成数(新)
        datarow["intimeFinishSpecCountNew"] = Convert.ToInt32(datarow["finishSpecCountNew"]) - Convert.ToInt32(datarow["delayFinishSpecCountNew"]);

        // 计算完成率A
        if (dtRateNew.Rows.Count > 0)
        {
            decimal finishRateNew = ((decimal)finishSpecCountNew / dtRateNew.Rows.Count) * 100;// 完成率 = 完工工序数 / 工序总数
            datarow["finishRateNew"] = finishRateNew.ToString("0.00");
        }
        else
        {
            datarow["finishRateNew"] = "--";
        }

        // 计算按时完成率A
        if (dtRateNew.Rows.Count > 0)
        {
            decimal intimeFinishRateNew = ((decimal)(finishSpecCountNew - delayFinishSpecCountNew) / dtRateNew.Rows.Count) * 100;
            datarow["intimeFinishRateNew"] = intimeFinishRateNew.ToString("0.00");//按时完成率
        }
        else
        {
            datarow["intimeFinishRateNew"] = "--";
        }

        r = new Result();
        r.data = dtCountInfo;
        r.total = 1;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getChart(JObject jObject)
    {
        DataTable dtRate = dal.GetDataTable(getSearchSqlString(jObject));
        Result r = new Result();
        if (dtRate.Rows.Count == 0)
        {
            r.data = new DataTable();
            r.total = 0;
            r.meta = new Result.Meta(200, "请求成功");
            return r;
        }
        foreach (DataRow dr in dtRate.Rows)
        {
            if (Convert.ToDecimal(dr["senddelaydays"]) < 0)
            {
                dr["senddelaydays"] = 0;
            }
            if (Convert.ToDecimal(dr["finishdelaydays"]) < 0)
            {
                dr["finishdelaydays"] = 0;
            }
        }

        DataTable dtResult = dtRate.DefaultView.ToTable(true, "fromfactory");
        dtResult.Columns.Add("specCount");//排产工序数
        dtResult.Columns.Add("intimeFinishSpecCount");//按时完工数
        dtResult.Columns.Add("delayFinishSpecCount");//超期完工数
        dtResult.Columns.Add("intimeFinishRate");//按时完成率

        int specCount, finishSpecCount, delayFinishSpecCount, intimeFinishSpecCount;

        foreach (DataRow row in dtResult.Rows)
        {
            specCount = dtRate.Select(string.Format("fromfactory='{0}'", row["fromfactory"].ToString())).Length;
            row["specCount"] = specCount;
            finishSpecCount = dtRate.Select(string.Format("fromfactory='{0}' AND recivedate is not null", row["fromfactory"].ToString())).Length;
            delayFinishSpecCount = dtRate.Select(string.Format("fromfactory='{0}' AND recivedate is not null and finishdelaydays <> 0", row["fromfactory"].ToString())).Length;
            intimeFinishSpecCount = finishSpecCount - delayFinishSpecCount;
            row["intimeFinishSpecCount"] = intimeFinishSpecCount;
            row["delayFinishSpecCount"] = delayFinishSpecCount;
            decimal intimeFinishRate = ((decimal)(intimeFinishSpecCount) / specCount) * 100;
            row["intimeFinishRate"] = intimeFinishRate.ToString("0.00");//按时完成率
        }

        DataRow datarow = dtResult.NewRow();
        datarow["fromfactory"] = "全部";

        //计算排产工序数
        specCount = dtRate.Rows.Count;
        datarow["specCount"] = specCount;

        //计算完工工序数
        finishSpecCount = dtRate.Select(" recivedate is not null ").Length;

        //计算超期完工数
        delayFinishSpecCount = dtRate.Select(" recivedate is not null and finishdelaydays <> 0 ").Length;
        datarow["delayFinishSpecCount"] = delayFinishSpecCount;

        //计算按时完工数
        datarow["intimeFinishSpecCount"] = finishSpecCount - delayFinishSpecCount;

        // 计算按时完成率
        if (specCount > 0)
        {
            decimal intimeFinishRate = ((decimal)(finishSpecCount - delayFinishSpecCount) / specCount) * 100;
            datarow["intimeFinishRate"] = intimeFinishRate.ToString("0.00");//按时完成率
        }
        else
        {
            datarow["intimeFinishRate"] = "--";
        }

        dtResult.Rows.Add(datarow);

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        r.data = dtResult;
        r.total = dtResult.Rows.Count;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getChartDetail(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        //主制分厂
        if (jObject["fromfactory"] != null && jObject["fromfactory"].ToString() != "全部")
        {
            sql += string.Format(" AND f.factoryname = '{0}'", jObject["fromfactory"].ToString());
        }

        if (jObject["seriesName"] != null)
        {
            if (jObject["seriesName"].ToString() == "按时完工数")
            {
                sql += " AND TRUNC(csr.recivedate) - TRUNC(csp.plannedfinishdate) <= 0";
            }
            if (jObject["seriesName"].ToString() == "超期完工数")
            {
                sql += " AND TRUNC(csr.recivedate) - TRUNC(csp.plannedfinishdate) > 0 ";
            }
        }

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataRow dr in dt.Rows)
        {
            if (Convert.ToDouble(dr["senddelaydays"].ToString()) < 0)
            {
                dr["senddelaydays"] = 0;
            }
            if (Convert.ToDouble(dr["finishdelaydays"].ToString()) < 0)
            {
                dr["finishdelaydays"] = 0;
            }
        }

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    string getSearchSqlString(JObject jObject, bool isSendOutNoDelayFilter = true)
    {
        string sql = @"
SELECT f.factoryname fromfactory,f2.factoryname tofactory,w.workcentername toworkcentername,cci.productname,cci.productdescription,
       cci.containername,cci.qty,csp.workflowstepname,cci.workflowstepname currentworkflowstepname,csp.stepplancreatedate,
       csp.plannedstartdate,csp.plannedfinishdate,csd.senddate,csd.toacceptdate,e.fullname receiver,csr.completedate,csr.recivedate,
       CASE WHEN csd.toacceptdate IS NOT NULL THEN TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate)
            ELSE TRUNC(SYSDATE) - TRUNC(csp.plannedstartdate)
       END senddelaydays,
       CASE WHEN csp.plannedfinishdate IS NOT NULL 
            THEN CASE WHEN csr.recivedate IS NOT NULL THEN TRUNC(csr.recivedate) - TRUNC(csp.plannedfinishdate)
                  ELSE TRUNC(SYSDATE) - TRUNC(csp.plannedfinishdate) 
                 END 
            ELSE 0 
       END finishdelaydays,
       --CASE WHEN dt2.rbdispatchdate IS NOT NULL THEN '已派工' ELSE '待派工' END isdispatch,
       CASE WHEN csr.completedate IS NULL AND TRUNC(SYSDATE) - TRUNC(csp.plannedfinishdate) > 0 THEN 'red'
            WHEN csr.completedate IS NOT NULL AND csr.recivedate IS NULL AND TRUNC(SYSDATE) - TRUNC(csr.completedate) > 1 THEN 'blue'
            WHEN csr.recivedate IS NOT NULL THEN 'black'
            WHEN csd.toacceptdate IS NOT NULL AND TRUNC(csp.plannedfinishdate) - TRUNC(csd.toacceptdate) <= 3 AND TRUNC(SYSDATE) - TRUNC(csp.plannedfinishdate) >= -1 THEN 'yellow'
            WHEN csd.toacceptdate IS NOT NULL AND TRUNC(csp.plannedfinishdate) - TRUNC(csd.toacceptdate) > 3 AND TRUNC(SYSDATE) - TRUNC(csp.plannedfinishdate) >= -2 THEN 'yellow'
       END color      
FROM containerstepplan csp
LEFT JOIN containercurrentinfo cci ON cci.containerid = csp.containerid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN factory f2 ON f2.factoryid = csp.specfactoryid
LEFT JOIN mesreport_containership_table mt ON mt.containerid = csp.containerid AND mt.workflowstepname = csp.workflowstepname
LEFT JOIN containershiprecord csd ON csd.containershiprecordid = mt.containershiprecordid AND csd.isuse = 0
LEFT JOIN containershiprecive csr ON csr.containershiprecordid = csd.containershiprecordid
LEFT JOIN workcenter w ON w.workcenterid = csd.toworkcenterid
LEFT JOIN employee e ON e.employeeid = csd.ToAcceptor --热表接收人
/*LEFT JOIN (
     SELECT sub.containerid,sub.workflowstepid,MAX(dt2.createdate) rbdispatchdate
     FROM containerstepdispatch2 dt2
     INNER JOIN containersubcontainer sub ON sub.subcontainerid = dt2.containerid
     WHERE dt2.isuse = 0
     GROUP BY sub.containerid,sub.workflowstepid
) dt2 ON dt2.containerid = csp.containerid AND dt2.workflowstepid = csp.workflowstepid*/
WHERE csp.plantype = 2 AND cci.containerstatus = 1 
";
        //承制分厂
        if (jObject["tofactoryid"] != null && jObject["tofactoryid"].ToString() != "")
        {
            sql += string.Format(" AND csp.specfactoryid = '{0}'", jObject["tofactoryid"].ToString());
        }

        //承制工区
        if (jObject["toworkcenterid"] != null && jObject["toworkcenterid"].ToString() != "")
        {
            sql += string.Format(" AND csd.toworkcenterid = '{0}'", jObject["toworkcenterid"].ToString());
        }

        //发出分厂
        if (jObject["fromfactoryid"] != null && jObject["fromfactoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["fromfactoryid"].ToString());
        }

        //零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        //计划完成日期
        if (jObject["plannedfinishdate"] != null && jObject["plannedfinishdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["plannedfinishdate"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND TRUNC(csp.plannedfinishdate) BETWEEN to_date('{0}','YYYY/MM/DD') AND to_date('{1}','YYYY/MM/DD') ", arr[0].ToString(), arr[1].ToString());
            }
        }

        //完成状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            if (jObject["status"].ToString() == "待完成")
            {
                sql += " AND csr.completedate IS NULL ";
            }
            else if (jObject["status"].ToString() == "待转入")
            {
                sql += " AND csr.completedate IS NOT NULL AND csr.recivedate IS NULL ";
            }
            else
            {
                sql += " AND csr.recivedate IS NOT NULL ";
            }
        }

        //派工状态
        //if (jObject["isdispatch"] != null && jObject["isdispatch"].ToString() != "")
        //{
        //    if (jObject["isdispatch"].ToString() == "已派工")
        //    {
        //        sql += " AND dt2.rbdispatchdate IS NOT NULL ";
        //    }
        //    else
        //    {
        //        sql += " AND dt2.rbdispatchdate IS NULL ";
        //    }
        //}

        //sql += " AND TRUNC(csp.plannedstartdate) BETWEEN (TRUNC(csp.stepplancreatedate) + 2) AND (TRUNC(csp.stepplancreatedate) + 8) ";

        //是否过滤未及时发出数据
        if (isSendOutNoDelayFilter)
        {
            sql += " AND TRUNC(csp.plannedstartdate) >= TRUNC(csd.toacceptdate) AND (TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate) = 0 OR TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate) = -1) ";
        }

        if (jObject["type"].ToString() != "getChartDetail")
        {
            sql += " ORDER BY csp.stepplancreatedate ";
        }
        return sql;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        Result r = new Result();
        string sql = getSearchSqlString(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataRow dr in dt.Rows)
        {
            if (Convert.ToDouble(dr["senddelaydays"].ToString()) < 0)
            {
                dr["senddelaydays"] = 0;
            }
            if (Convert.ToDouble(dr["finishdelaydays"].ToString()) < 0)
            {
                dr["finishdelaydays"] = 0;
            }
        }

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    string getSendNotInTimeDetailsSql(JObject jObject)
    {
        string sql = @"
SELECT t.*
FROM (
SELECT f.factoryname fromfactory,f2.factoryname tofactory,w.workcentername toworkcentername,cci.productname,cci.productdescription,
       cci.containername,cci.qty,csp.workflowstepname,cci.workflowstepname currentworkflowstepname,csp.stepplancreatedate,
       csp.plannedstartdate,csp.plannedfinishdate,csd.senddate,csd.toacceptdate,e.fullname receiver,csr.completedate,csr.recivedate,
       CASE WHEN csd.toacceptdate IS NOT NULL THEN TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate)
            ELSE TRUNC(SYSDATE) - TRUNC(csp.plannedstartdate)
       END senddelaydays,
       CASE WHEN csp.plannedfinishdate IS NOT NULL 
            THEN CASE WHEN csr.recivedate IS NOT NULL THEN TRUNC(csr.recivedate) - TRUNC(csp.plannedfinishdate)
                  ELSE TRUNC(SYSDATE) - TRUNC(csp.plannedfinishdate) 
                 END 
            ELSE 0 
       END finishdelaydays,
       CASE WHEN dt.createdate IS NOT NULL THEN '已派工' ELSE '待派工' END isdispatch
FROM containerstepplan csp
LEFT JOIN containercurrentinfo cci ON cci.containerid = csp.containerid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN factory f2 ON f2.factoryid = csp.specfactoryid
LEFT JOIN mesreport_containership_table mt ON mt.containerid = csp.containerid AND mt.workflowstepname = csp.workflowstepname
LEFT JOIN containershiprecord csd ON csd.containershiprecordid = mt.containershiprecordid AND csd.isuse = 0
LEFT JOIN containershiprecive csr ON csr.containershiprecordid = csd.containershiprecordid 
LEFT JOIN employee e ON e.employeeid = csd.ToAcceptor --热表接收人
LEFT JOIN workcenter w ON w.workcenterid = csd.toworkcenterid
LEFT JOIN containerstepdispatch dt ON dt.containerid = csp.containerid AND dt.workflowstepid = csp.workflowstepid AND dt.isuse = 0 
WHERE csp.plantype = 2 AND cci.containerstatus = 1
";
        //承制分厂
        if (jObject["tofactoryid"] != null && jObject["tofactoryid"].ToString() != "")
        {
            sql += string.Format(" AND csp.specfactoryid = '{0}'", jObject["tofactoryid"].ToString());
        }

        //承制工区
        if (jObject["toworkcenterid"] != null && jObject["toworkcenterid"].ToString() != "")
        {
            sql += string.Format(" AND csd.toworkcenterid = '{0}'", jObject["toworkcenterid"].ToString());
        }

        //发出分厂
        if (jObject["fromfactoryid"] != null && jObject["fromfactoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["fromfactoryid"].ToString());
        }

        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
        }

        //零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        //计划完成日期
        if (jObject["plannedfinishdate"] != null && jObject["plannedfinishdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["plannedfinishdate"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND TRUNC(csp.plannedfinishdate) BETWEEN to_date('{0}','YYYY/MM/DD') AND to_date('{1}','YYYY/MM/DD') ", arr[0].ToString(), arr[1].ToString());
            }
        }

        //机加排产日期
        //if (jObject["plancreatedateList"] != null && jObject["plancreatedateList"].ToString() != "")
        //{
        //    JArray arr = JArray.Parse(jObject["plancreatedateList"].ToString());
        //    if (arr.Count > 0)
        //    {
        //        string list = "";
        //        for (int i = 0; i < arr.Count; i++)
        //        {
        //            list += string.Format("to_date('{0}','YYYY/MM/DD'),", arr[i].ToString());
        //        }
        //        list = list.Remove(list.Length - 1, 1);
        //        sql += string.Format(" AND TRUNC(csp.stepplancreatedate) IN ({0})", list);
        //    }
        //}

        //完成状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            if (jObject["status"].ToString() == "待完成")
            {
                sql += " AND csr.completedate IS NULL ";
            }
            else if (jObject["status"].ToString() == "待转入")
            {
                sql += " AND csr.completedate IS NOT NULL AND csr.recivedate IS NULL ";
            }
            else
            {
                sql += " AND csr.recivedate IS NOT NULL ";
            }
        }

        //派工状态
        if (jObject["isdispatch"] != null && jObject["isdispatch"].ToString() != "")
        {
            if (jObject["isdispatch"].ToString() == "已派工")
            {
                sql += " AND dt.createdate IS NOT NULL ";
            }
            else
            {
                sql += " AND dt.createdate IS NULL ";
            }
        }

        //sql += @" AND TRUNC(csp.plannedstartdate) BETWEEN (TRUNC(csp.stepplancreatedate) + 2) AND (TRUNC(csp.stepplancreatedate) + 8) ";
        sql += " ) t WHERE t.senddelaydays <> 0 AND t.senddelaydays <> -1 ";
        return sql;
    }

    //查询主制分厂未及时发出数据统计
    Result getSendNotInTimeChartData(JObject jObject)
    {
        string sql = @"
SELECT t.fromfactory,COUNT(t.fromfactory) qty
FROM (
    {0}
) t
GROUP BY t.fromfactory
";
        sql = string.Format(sql, getSendNotInTimeDetailsSql(jObject));
        Result r = new Result();
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //主制分厂未及时发出明细
    Result getSendNotInTimeDetails(JObject jObject)
    {
        Result r = new Result();
        string sql = getSendNotInTimeDetailsSql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        foreach (DataRow dr in dt.Rows)
        {
            if (Convert.ToDouble(dr["senddelaydays"].ToString()) < 0)
            {
                dr["senddelaydays"] = 0;
            }
            if (Convert.ToDouble(dr["finishdelaydays"].ToString()) < 0)
            {
                dr["finishdelaydays"] = 0;
            }
        }
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        DataTable dtResult = dal.GetDataTable(sql);

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        foreach (DataRow dr in dtResult.Rows)
        {
            if (Convert.ToDouble(dr["senddelaydays"].ToString()) < 0)
            {
                dr["senddelaydays"] = 0;
            }
            if (Convert.ToDouble(dr["finishdelaydays"].ToString()) < 0)
            {
                dr["finishdelaydays"] = 0;
            }
        }

        if (dtResult.Columns.Contains("senddelaydays"))
        {
            dtResult.Columns.Remove("senddelaydays");
        }
        dtResult.Columns.Remove("color");
        dtResult.Columns["fromfactory"].Caption = "主制分厂";
        dtResult.Columns["tofactory"].Caption = "承制分厂";
        dtResult.Columns["toworkcentername"].Caption = "承制工区";
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["containername"].Caption = "流水卡号";
        dtResult.Columns["qty"].Caption = "数量";
        dtResult.Columns["workflowstepname"].Caption = "排产工序";
        dtResult.Columns["plannedstartdate"].Caption = "plannedstartdate";
        dtResult.Columns["senddate"].Caption = "实际发出时间";
        dtResult.Columns["toacceptdate"].Caption = "接收时间";
        dtResult.Columns["receiver"].Caption = "接收人";
        dtResult.Columns["completedate"].Caption = "实际完成时间";
        dtResult.Columns["recivedate"].Caption = "转入时间";
        dtResult.Columns["currentworkflowstepname"].Caption = "当前工序";
        dtResult.Columns["finishdelaydays"].Caption = "完成超期时间";
        dtResult.Columns["plannedstartdate"].Caption = "计划发出时间";
        dtResult.Columns["plannedfinishdate"].Caption = "计划完成时间";
        dtResult.Columns["stepplancreatedate"].Caption = "排产日期";
        //dtResult.Columns["isdispatch"].Caption = "派工状态";
        ExportHelper.ExportExcel(dtResult, "热表转工排产报表");
    }

    //主制分厂未及时发出明细--导出
    void exportSendNotInTimeDetails(JObject jObject)
    {
        string sql = getSendNotInTimeDetailsSql(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        foreach (DataRow dr in dtResult.Rows)
        {
            if (Convert.ToDouble(dr["senddelaydays"].ToString()) < 0)
            {
                dr["senddelaydays"] = 0;
            }
            if (Convert.ToDouble(dr["finishdelaydays"].ToString()) < 0)
            {
                dr["finishdelaydays"] = 0;
            }
        }
        dtResult.Columns["fromfactory"].Caption = "主制分厂";
        dtResult.Columns["tofactory"].Caption = "承制分厂";
        dtResult.Columns["toworkcentername"].Caption = "承制工区";
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["containername"].Caption = "流水卡号";
        dtResult.Columns["qty"].Caption = "数量";
        dtResult.Columns["workflowstepname"].Caption = "排产工序";
        dtResult.Columns["plannedstartdate"].Caption = "plannedstartdate";
        dtResult.Columns["senddate"].Caption = "实际发出时间";
        dtResult.Columns["toacceptdate"].Caption = "接收时间";
        dtResult.Columns["receiver"].Caption = "接收人";
        dtResult.Columns["completedate"].Caption = "实际完成时间";
        dtResult.Columns["recivedate"].Caption = "转入时间";
        dtResult.Columns["currentworkflowstepname"].Caption = "当前工序";
        dtResult.Columns["finishdelaydays"].Caption = "完成超期时间";
        dtResult.Columns["plannedstartdate"].Caption = "计划发出时间";
        dtResult.Columns["plannedfinishdate"].Caption = "计划完成时间";
        dtResult.Columns["stepplancreatedate"].Caption = "排产日期";
        dtResult.Columns["senddelaydays"].Caption = "发出超期时间";
        ExportHelper.ExportExcel(dtResult, jObject["status"].ToString() + "未及时发出明细");
    }
}